<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/**
 * Includes the Nested_Set_Model class as well as the required sub-classes
 * @package codeigniter.application.models
 */

/**
 * User_Nested_Set_Model extends codeigniters base CI_Model to inherit all codeigniter magic!
 * @author Com-RT
 * @package codeigniter.application.models
 */
class Smartphone_Model extends CI_Model {
    /*
     * A private variable to represent each column in the database-table
     */

    private $tablename = 'smartphone AS SP';

    function __construct() {
        parent::__construct();
    }

    function getItem($id) {
        $this->db->select('*,(SELECT AVG(R.rating)FROM sp_rating AS R INNER JOIN sp_has_rating AS SPHR ON R.rating_id = SPHR.rating_id WHERE SPHR.sp_id = SP.id) AS Rating ');
        $this->db->join('os', 'os.os_id = SP.os_id');
        $this->db->join('chip', 'chip.chip_id = SP.chip_id');
        $this->db->join('manufacturer', 'manufacturer.manufacturer_id = SP.manufacturer_id');
        $this->db->join('price_class', 'price_class.price_class_id = SP.price_class_id');
        $this->db->join('sp_resolution', 'sp_resolution.sp_resolution_id = SP.sp_resolution_id');        
	$item = $this->db->get_where($this->tablename,array('id' => $id)); 
        
        return $item->row();//gibt eine row als Objekt zur�ck
	
    }

    function getItems() {

        $this->db->select('*, '
                . '(SELECT AVG(R.rating) FROM sp_rating AS R INNER JOIN sp_has_rating AS SPHR ON R.rating_id = SPHR.rating_id WHERE SPHR.sp_id = SP.id) AS Rating,'
                .' (SELECT SUM(R.rating) FROM sp_rating AS R INNER JOIN sp_has_rating AS SPHR ON R.rating_id = SPHR.rating_id WHERE SPHR.sp_id = SP.id) AS RatingCount');
        $this->db->select('');
        $this->db->from('smartphone AS SP');
        $this->db->join('os', 'os.os_id = SP.os_id');
        $this->db->join('chip', 'chip.chip_id = SP.chip_id');
        $this->db->join('manufacturer', 'manufacturer.manufacturer_id = SP.manufacturer_id');
        $this->db->join('price_class', 'price_class.price_class_id = SP.price_class_id');
        $this->db->join('sp_resolution', 'sp_resolution.sp_resolution_id = SP.sp_resolution_id');
        $this->db->order_by('SP.id','ASC');
        
        $items = $this->db->get();

        return $items->result(); //gibt die ergebnisse als array von Objekten zurück
    }

    //object reinreichen, id des Objekts zurückbekommen
    function update($item) {
        $this->db->where('id', $item->id);
        $this->db->update($this->tablename, $item);
    }

    //mit insert-id rückgabe
    function insert($item) {

        $this->db->insert($this->tablename, $item);
        $insert_id = $this->db->insert_id();
        $this->db->trans_complete();
        return $insert_id;
    }

    function delete($item) {
        $this->db->delete($this->tablename, array('id' => $item->id));
    }

}
